package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.X_MM_Waybill;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;

public class MM_Form_8 extends SvrProcess {
	//Form_8_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	
	Timestamp p_MM_DateOf_IssueBegin; 
	Timestamp p_MM_DateOf_IssueEnd; 

	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("MM_DateOf_IssueBegin"))
				p_MM_DateOf_IssueBegin = (Timestamp) para[i].getParameter();
			else if (name.equals("MM_DateOf_IssueEnd"))
				p_MM_DateOf_IssueEnd = (Timestamp) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		
	}


	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_8_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 8) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		// Cell Styling
		CellStyle ds = wb.createCellStyle();
		ds.setBorderBottom((short)1);
		ds.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		ds.setDataFormat(wb.createDataFormat().getFormat("dd.MM.yyyy"));
		ds.setAlignment(CellStyle.ALIGN_CENTER);
		
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom((short)1);
		mergeStyle.setBorderTop((short)1);
		mergeStyle.setBorderLeft((short)1);
		mergeStyle.setBorderRight((short)1);
		mergeStyle.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		mergeStyle.setWrapText(true);
		mergeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		//Header
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(12).getCell(24).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueBegin)));
		sheet.getRow(12).getCell(30).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueEnd)));
		
		//Positions
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT MM_Waybill_ID FROM MM_Waybill " + 
					"WHERE MM_DateOf_Issue >= '" + new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueBegin) + "'" + 
					" AND MM_DateOf_Issue <= '" + new SimpleDateFormat("dd.MM.yyyy").format(p_MM_DateOf_IssueEnd) + "'";
		
		try {
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int row = 23;
		
			while (rs.next()) {
				X_MM_Waybill waybill = new X_MM_Waybill(m_ctx, rs.getInt(1), get_TrxName());
				
				//Shifting and Merging
				sheet.shiftRows(row, row + 14, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 6, 10));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 11, 18));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 19, 24));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 25, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 38));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 39, 44));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 45, 50));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 58));
				
				R = sheet.createRow(row);
				for(int i=0; i<=58;i++){
					C = R.createCell(i);
					C.setCellStyle(mergeStyle);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(waybill.getDocumentNo());
				sheet.getRow(row).getCell(6).setCellValue(waybill.getMM_DateOf_Issue());
				sheet.getRow(row).getCell(6).setCellStyle(ds);
				sheet.getRow(row).getCell(11).setCellValue((new MBPartner(m_ctx, waybill.getMM_Driver_ID(), get_TrxName()).getName()));

				sheet.getRow(row).getCell(25).setCellValue(waybill.getMM_Vehicle_RegNo());
				
				PreparedStatement pstmttn = null;
				ResultSet rstn = null;
				String sqltn = "SELECT hrm_tableNumber FROM C_BPartner " + 
				"WHERE C_BPartner_ID = " + waybill.getMM_Driver_ID();
				
				try {
					pstmttn = DB.prepareStatement(sqltn.toString(), get_TrxName());
					rstn = pstmttn.executeQuery();
					
					while (rstn.next()) {
						sheet.getRow(row).getCell(19).setCellValue(rstn.getInt(1));
					}
				}
				catch (Exception e) {
					log.log(Level.SEVERE, sqltn.toString(), get_TrxName());
				}
				finally {
					DB.close(rstn, pstmttn);
					rstn = null;
					pstmttn = null;
				}
				row++;
			}
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
	
		//---------------------------------------------------------------
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
